This invention relates generally to information processing, and, more particularly, to methods and apparatuses for generating and distributing reports from a relational database.
Graphical reports are commonly used to visually express information about selected properties or characteristics of various entities. For example, a report may contain a pie chart to express the relative profitability or expenses of various corporate divisions, where each division is represented by a slice of the pie. Typically, the information needed to generate a report is derived from a relational database that may contain, for example, a table showing the expenses and revenues of each division in a corporation. Detailed breakdowns of the expenses of each division might also be provided in other tables in the database.
When displaying a report, such as a pie chart, about certain properties or characteristics of various entities, it is often desirable to allow the user to select one of the slices (using, for example, a point-and-click device such as a computer mouse) in order to “drill down” to more detailed information about that particular slice. Continuing the pie chart example described above, one might configure the report so that if one selected one of the slices representing a corporate division, the computer would generate a new report displaying two more pie charts that broke down the profits and expenses of the division into separate categories.
Drill down capabilities can already be found in several retail applications. For example, the popular financial tracking application known as Quicken®, made by Intuit Inc., provides several built-in reports that enable a user to drill-down to more detailed reports. While Quicken® provides a familiar illustration of drill-down reports, its capabilities are provided specifically only for the specialized database that comes with the application. Moreover, that specialized application does not, and is not intended to, provide a reporting tool and drill-down convention by which an end-user or customer can define the reports to be generated and customize the drill-down relationships between them.
There are a variety of conceivable situations in which a company may want to develop its own set of inter-related reports from its own database, add drill-down capabilities, and also define what report(s) is/are displayed when a given pie slice, 3-D bar, or cross-tab cell is selected. There are some database reporting tools on the market that are designed to give a customer such power, including, for example, Cognos, Inc.'s “Improptu”®, and Crystal Decisions Co.'s “Crystal Reports.”® Unfortunately for the customer, typical drilldown-capable reporting tools are not intuitively, dynamically, or easily implemented. Rather, typical drilldown-capable reporting tools require the customer to write additional code blocks in languages other than in the database query language (“DQL”) used to interrogate the relational databases, in order to link one report to another. Typically, a separate block of programming code in a second language such as PL/SQL, Visual Basic, C++, Java, or JavaScript, has to be written, tested, debugged, and compiled for each report-to-report relationship. Alternatively, the programmer may have to use one of the vendor's specialized data structures. This makes report building a tedious, technically difficult, time-consuming, and expensive task.
U.S. Pat. Nos. 5,603,025 and 5,787,416, both to Tabb et al., which are herein incorporated by reference, describe a system that automatically recognizes related information by looking for primary keys that uniquely identify records in a given table. The system also automatically generates hyperlinked reports, as illustrated by FIGS. 6A through 6E of those patents, that enable an end-user to drill down to increasing levels of detail. As suggested by column 3, lines 17–25, the Tabb et al. inventions are intended to completely bypass the need for an end-user to use computer programming languages to create reports with drill-down functions. While the Tabb et al. patents describe a system with considerable utility, the automation and ease of use comes at the expense of the customer's ability to customize relationships between reports.
There is a need a system for specifying drill-down relationships between reports that is neither overly complex nor overly restrictive. In particular, there is a need for an intuitive, DQL-based or DQL-consistent reporting convention and/or tool that does not require the writing, debugging, and compiling of code blocks to define drill-down relationships between reports, where the code blocks are separate from the DQL queries that are used to generate the result set from which the report is generated. There is also a need for a reporting convention and/or tool that does not require multi-lingual implementation (e.g., structured query language and a complementary procedural programming language such as Microsoft Corporation's Visual Basic®).
A summary and detailed description of the invention is provided below. But first, for the benefit of readers having little or no familiarity with relational databases or related concepts, a very brief introduction to relational databases and relational database terminology is provided.
A relational database is, in the abstract, a collection of “relations.” For most purposes, however, a relational database is better understood as a collection of tables. A relation (e.g., a table) comprises one or more entities (e.g., rows, a.k.a. “records” or “tuples”) that are identified by certain characteristics, properties, or attributes (e.g., columns, a.k.a. “fields”). A table in a relational database has the following intuitive properties: each column describes a given characteristic, property, or attribute; each column is distinctly named; all values of a given column are of the same type; each row in the table is unique; and the relational properties of the database are not affected by column or row order. Moreover, the number of rows and columns in a table need not be fixed. In this respect, a table is distinguishable from a matrix or array, which have fixed row and column dimensions.
Typically, a relational database contains a plurality of tables that can be interrelated with each other because one or more properties in one table matches one or more properties in other tables. FIG. 4, for example, provides an example of a relational database structure 400 having five different relations. The “Products” relation 410 is shown with five properties labeled “ProductID,” “ProductName,” “CategoryID,” “QuantityPerUnit,” and “UnitPrice.” The “Order—Details” relation 420 is shown with four properties labeled “OrderID,” “ProductID,” “Quantity,” and “UnitPrice.” Relations 410 and 420 each share a common property “ProductID.” Likewise, the Orders relation 430 shares the property “OrderID” with the Order—Details relation 420, the Employees relation 450 shares the property “EmployeeID” with the Orders relation 450; and the Categories relation 440 shares the property “CategoryID” with the Products relation 410.
A database built in accordance with the relation 410 of FIG. 4 would typically contain 5 tables corresponding with each of the relations 410, 420, 430, 440, and 450. The table corresponding to relation 410, for example, would have five columns. The first row would contain the headings for the columns, that is, “ProductID,” “ProductName,” “CategoryID,” “QuantityPerUnit,” and “UnitPrice.” Below the first row would be a plurality of rows describing different products in accordance with the column headings. The other tables would also have column headings corresponding to each of the properties of the associated relation, and rows below them describing various orders, order details, product categories, employees, and so on.
Relational databases are designed to be powerful, flexible ways of storing, categorizing, and associating data. The power of a relational database is illustrated by the following example. Using the relational database structure described in FIG. 4, suppose someone wanted to find out how many Widget Class products, a special category of products listed in the Categories relation 440, that a particular Employee had sold in a given year. To do so, one would use the Employees relation 450 to identify the EmployeeID associated with that particular employee. Then that EmployeeID, along with the specified year, would be used to filter out all the OrderIDs listed in the Orders relation 430 that had the same EmployeeID and an OrderDate falling within the specified year. Similarly, the Categories relation 440 would be used to identify the CategoryID associated with the Widget Class category of products. Then the Products relation 410 would be used to filter out all of the ProductIDs associated with the identified CategoryID. Next, the Order—Details relation 420 would be used to identify all the orders that had both one of the OrderIDs identified above, and one of the ProductIDs identified above. From this final subset of orders, the sum of the products of Quantity times UnitPrice would be computed to determine the result.
A relational database management system (RDBMS) is an interface between a user and a relational database that allows the user to create, modify, update, and delete relations such as those shown in FIG. 4, as to well as to retrieve information like that described above by linking various relations together. In the past two decades, the software industry has largely standardized the syntax used to create, manipulate, delete, and update information in a relational database. This most widely accepted set of query language standards is “structured query language,” also known by its acronym SQL. Those skilled in the art are familiar with many alternative database query languages, each having its own particular syntax. For example, query language syntax standards have been proposed for Extensible Markup Language (“XML”).